package com.ibuonline.b2b.common.importer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.dimeng.common.util.StringUtil;

public class ColumnHandleUtil {
	public static Connection getConnection() throws Exception {
		String url = "jdbc:mysql://192.168.0.229:3306/b2b?characterEncoding=GBK";
		String user = "b2b";
		String password = "b2b";
		Class.forName("org.gjt.mm.mysql.Driver");
		return DriverManager.getConnection(url, user, password);
	}
	
	public static void main(String[] args) {
		String selectSql = "SELECT * FROM prod_attribute p WHERE p.value LIKE '%<script%'";
		String updateSql = "update prod_attribute p set p.value=? where p.value=?";
		Connection conn = null;
		PreparedStatement pstm1 = null;
		PreparedStatement pstm2 = null;
		ResultSet rs = null;
		try {
			conn = getConnection();
			conn.setAutoCommit(false);
			pstm1 = conn.prepareStatement(selectSql);
			pstm2 = conn.prepareStatement(updateSql);
			rs = pstm1.executeQuery();
			while(rs.next()) {
				String value = rs.getString("value");
				System.out.println(value);
				pstm2.setString(1, StringUtil.clearScriptCode(value));
				pstm2.setString(2, value);
				pstm2.executeUpdate();
			}
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if(pstm2 != null) {
					pstm2.close();
					pstm2 = null;
				}
				if(pstm1 != null) {
					pstm1.close();
					pstm1 = null;
				}
				if(rs != null) {
					rs.close();
					rs = null;
				}
				if(conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}
